package com.ismartv.sale.util;

import com.ismartv.sale.bean.RegionBean;
import com.ismartv.sale.dao.DaoManager;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.LinkedList;

public class ExportExcel {

	private HSSFWorkbook wb = null;
	private HSSFSheet sheet = null;
	private HSSFCellStyle cellStyle;
	private LinkedList<RegionBean> regionlist = new LinkedList<RegionBean>();

	/**
	 * @param wb
	 * @param sheet
	 */
	public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {
		this.wb = wb;
		this.sheet = sheet;
		cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setWrapText(true);
		fillRegionTitleData();
	}

	public void createRegionHead() {
		DaoManager dbm = new DaoManager();
		HashMap<String, Integer> datamaps = dbm.getOfflineData("20140325",
				"20140625");
		HSSFRow rowtitle = sheet.createRow(0);
		HSSFRow rowregion = sheet.createRow(1);
		HSSFRow province = sheet.createRow(2);
		HSSFRow rowdata = sheet.createRow(3);

		sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
		HSSFCell ce = rowtitle.createCell(0, Cell.CELL_TYPE_STRING);
		ce.setCellValue("日期（FY14-15 第一财季）");
		ce.setCellStyle(cellStyle);
		// first colunm,title
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 35));
		HSSFCell title = rowtitle.createCell(1, Cell.CELL_TYPE_STRING);
		title.setCellValue("当日激活数量（线下机型：省/分区维度）");
		title.setCellStyle(cellStyle);

		int temp = 0;
		int celln = 0;
		int celle = 0;
		for (int i = 0; i < regionlist.size(); i++) {
			celln = celle;
			temp += regionlist.get(i).getProvinceCount();
			celle = temp;
			if (regionlist.get(i).getProvinceCount() > 1) {
				celle = temp;
				sheet.addMergedRegion(new CellRangeAddress(1, 1, celln + 1,
						celle));
			}
			HSSFCell cell = rowregion.createCell(celln + 1,
					Cell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(regionlist.get(i)
					.getRegionName()));
			cell.setCellStyle(cellStyle);
			LinkedList<String> provinces = regionlist.get(i).getProvices();
			for (int m = 0; m < provinces.size(); m++) {
				HSSFCell cell1 = province.createCell(celln + 1 + m,
						Cell.CELL_TYPE_STRING);
				cell1.setCellValue(new HSSFRichTextString(provinces.get(m)));
				cell1.setCellStyle(cellStyle);
				// report data
				HSSFCell datacell = rowdata.createCell(celln + 1 + m,
						Cell.CELL_TYPE_STRING);
				System.out.println("proviceName = " + provinces.get(m));
				if (datamaps.get(provinces.get(m)) == null) {
					datacell.setCellValue("0");
				} else {
					datacell.setCellValue(datamaps.get(provinces.get(m)));
				}
				datacell.setCellStyle(cellStyle);
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 2, celle + 1, celle + 1));
		HSSFCell cell = rowregion.createCell(celle + 1, Cell.CELL_TYPE_STRING);
		cell.setCellValue("当期线下机型激活合计");
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 设置报表标题
	 * 
	 * @param columHeader
	 *            标题字符串数组
	 */
	public void createColumHeader(String[] columHeader) {

		// 设置列头 在第三行
		HSSFRow row2 = sheet.createRow(2);

		// 指定行高
		row2.setHeight((short) 600);

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行

		// 单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 250);
		cellStyle.setFont(font);

		// 设置单元格背景色
		cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		HSSFCell cell3 = null;

		for (int i = 0; i < columHeader.length; i++) {
			cell3 = row2.createCell(i);
			cell3.setCellType(HSSFCell.ENCODING_UTF_16);
			cell3.setCellStyle(cellStyle);
			cell3.setCellValue(new HSSFRichTextString(columHeader[i]));
		}
	}


	/**
	 * 输入EXCEL文件
	 * 
	 * @param fileName
	 *            文件名
	 */
	public void outputExcel(String fileName) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(new File(fileName));
			wb.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @return the sheet
	 */
	public HSSFSheet getSheet() {
		return sheet;
	}

	/**
	 * @param sheet
	 *            the sheet to set
	 */
	public void setSheet(HSSFSheet sheet) {
		this.sheet = sheet;
	}

	/**
	 * @return the wb
	 */
	public HSSFWorkbook getWb() {
		return wb;
	}

	/**
	 * @param wb
	 *            the wb to set
	 */
	public void setWb(HSSFWorkbook wb) {
		this.wb = wb;
	}

	private void fillRegionTitleData() {
		LinkedList<String> heiji = new LinkedList<String>();
		heiji.add("黑龙江");
		heiji.add("吉林");
		LinkedList<String> liaoning = new LinkedList<String>();
		liaoning.add("辽宁");

		LinkedList<String> jingjinji = new LinkedList<String>();
		jingjinji.add("河北");
		jingjinji.add("北京");
		jingjinji.add("天津");
		LinkedList<String> shandong = new LinkedList<String>();
		shandong.add("山东");
		LinkedList<String> henan = new LinkedList<String>();
		henan.add("河南");
		LinkedList<String> jinmeng = new LinkedList<String>();
		jinmeng.add("山西");
		jinmeng.add("内蒙古");
		LinkedList<String> xibei = new LinkedList<String>();
		xibei.add("陕西");
		xibei.add("甘肃");
		xibei.add("宁夏");
		xibei.add("青海");
		xibei.add("新疆");
		LinkedList<String> jiangsu = new LinkedList<String>();
		jiangsu.add("江苏");

		LinkedList<String> huadong = new LinkedList<String>();
		huadong.add("江西");
		huadong.add("浙江");
		huadong.add("上海");
		LinkedList<String> fujian = new LinkedList<String>();
		fujian.add("福建");
		LinkedList<String> huazhong = new LinkedList<String>();
		huazhong.add("湖北");
		huazhong.add("安徽");
		LinkedList<String> hunan = new LinkedList<String>();
		hunan.add("湖南");
		LinkedList<String> gaungdong = new LinkedList<String>();
		gaungdong.add("广东");

		LinkedList<String> shengui = new LinkedList<String>();
		shengui.add("广西");
		shengui.add("海南");
		shengui.add("深圳");
		LinkedList<String> chuanyu = new LinkedList<String>();
		chuanyu.add("四川");
		chuanyu.add("重庆");

		LinkedList<String> yungui = new LinkedList<String>();
		yungui.add("云南");
		yungui.add("贵州");

		LinkedList<String> hanlinhui = new LinkedList<String>();
		hanlinhui.add("翰林汇");
		LinkedList<String> weishi = new LinkedList<String>();
		weishi.add("伟仕");
		LinkedList<String> wangxin = new LinkedList<String>();
		wangxin.add("网新");

		regionlist.add(new RegionBean("黑吉", heiji, heiji.size()));
		regionlist.add(new RegionBean("辽宁", liaoning, liaoning.size()));
		regionlist.add(new RegionBean("京津冀", jingjinji, jingjinji.size()));
		regionlist.add(new RegionBean("山东", shandong, shandong.size()));
		regionlist.add(new RegionBean("河南", henan, henan.size()));
		regionlist.add(new RegionBean("晋蒙", jinmeng, jinmeng.size()));
		regionlist.add(new RegionBean("西北", xibei, xibei.size()));
		regionlist.add(new RegionBean("江苏", jiangsu, jiangsu.size()));
		regionlist.add(new RegionBean("华东", huadong, huadong.size()));
		regionlist.add(new RegionBean("福建", fujian, fujian.size()));
		regionlist.add(new RegionBean("华中", huazhong, huazhong.size()));
		regionlist.add(new RegionBean("湖南", hunan, hunan.size()));
		regionlist.add(new RegionBean("广东", gaungdong, gaungdong.size()));
		regionlist.add(new RegionBean("深桂", shengui, shengui.size()));
		regionlist.add(new RegionBean("川渝", chuanyu, chuanyu.size()));
		regionlist.add(new RegionBean("云贵", yungui, yungui.size()));
		regionlist.add(new RegionBean("翰林汇", hanlinhui, hanlinhui.size()));
		regionlist.add(new RegionBean("伟仕", weishi, weishi.size()));
		regionlist.add(new RegionBean("网新", wangxin, wangxin.size()));
	}

}
